from django.shortcuts import render, redirect
from django.views import View
from django import http
import json
from demo_book.models import BookInfo, HeroInfo
from django.db.models import F, Q, Avg, Min, Max, Sum
import datetime

# Create your views here.
'''
用于测试数据库
实现数据的增删改查
'''


# ----------------------数据库增删改的视图-----------------------------
# http://127.0.0.1:8000/bookinfo/
class BookinfoView( View ):

	def get(self, request):
		# ***************************** --关联查询-- *****************************
		# 一对多
		# 查询id=1的图书
		# book = BookInfo.objects.get( id=1 )
		# # 关联英雄表，匹配属于id=1的英雄
		# hero = book.heroinfo_set.all()
		# print( hero )
		#
		# 多对一
		# hero = HeroInfo.objects.filter( Q( id=5 ) | Q( hcomment='降龙十八掌' ) )
		# print( hero )
		#
		# for i in hero:
		# 	hbook = i.hbook
		# 	print( i.hname, i.hcomment, hbook )

		# 多对一
		# h = HeroInfo.objects.get( id=3 )
		# print( h.hbook )

		# *****************************---order_by() - --排序查询*****************************

		# --------------------order_by() - --排序查询-----------------

		# 图书名不为空的图书，按照阅读值升序 小-->大 排序
		# book1 = BookInfo.objects.filter( btitle__isnull=False ).order_by( 'bread' )
		# # 图书名不为空的图书，按照阅读值降序 大-->小 排序
		# book2 = BookInfo.objects.filter( btitle__isnull=False ).order_by( '-bread' )
		# print( '图书名不为空的图书，按照阅读值升序 小-->大', book1 )
		# print( '图书名不为空的图书，按照阅读值降序 大-->小', book2 )

		# *****************************---AVg(),Count(),Max(),Min(),Sum() - --聚合函数查询*****************************

		# -----------------*---AVg(),Count(),Max(),Min(),Sum() - --聚合函数查询-----------------

		# 图书阅读量的平均值
		# book1 = BookInfo.objects.aggregate( Avg( 'bread' ) )
		# # # 图书的和
		# book2 = BookInfo.objects.all().count()
		# #阅读大于30书籍总数
		# book2s = BookInfo.objects.filter( bread__gt=30 ).count()
		# # 图书阅读量的最大值
		# book3 = BookInfo.objects.aggregate( Max( 'bread' ) )
		# # 图书阅读量的最小值
		# book4 = BookInfo.objects.aggregate( Min( 'bread' ) )
		# # 图书阅读量的和
		# book5 = BookInfo.objects.aggregate( Sum( 'bread' ) )
		# print( '图书阅读量的平均值', book1 )
		# print( '图书的和', book2 )
		# print( '阅读大于30书籍总数', book2s )
		# print( '图书阅读量的最大值', book3 )
		# print( '图书阅读量的最小值', book4 )
		# print( '图书阅读量的和', book5 )

		# *****************************---F,Q - --比较查询*****************************

		# -----------------F - --比较查询-----------------

		# # 1.阅读>=评论的图书
		# book1 = BookInfo.objects.filter( bread__gte=F( 'bcomment' ) )
		# # 2.阅读>=评论的图书*2
		# book2 = BookInfo.objects.filter( bread__gte=F( 'bcomment' ) * 2 )
		# print( '1', book1 )
		# print( '2', book2 )

		# -----------------Q - --比较查询-----------------

		# and,or,not 与或非
		# 与
		# 阅读量>=30且id>2
		# book1 = BookInfo.objects.filter( bread__gte=30, id__gt=2 )
		# # 或
		# # 阅读量>=30或id>2
		# book2 = BookInfo.objects.filter( Q( bread__gte=30 ) | Q( id__gt=2 ) )
		# # 非
		# # 非逻辑删除的数据
		# book3 = BookInfo.objects.filter( ~Q( is_delete__isnull=False ) )
		# book4 = BookInfo.objects.filter( ~Q( id=3 ) )
		#
		# print( '阅读量>=30且id>2', book1 )
		# print( '阅读量>=30或id>2', book2 )
		# print( '非逻辑删除的数据', book3 )
		# print( '非逻辑删除的数据', book4 )

		# *****************************---filter() - --过滤查询*****************************

		# -----------------filter() - --过滤查询-----------------

		# gt 大于
		# lt 小于
		# gte >=
		# lte <=
		# contains 包含
		# startswith 以..开头
		# endswith   以..结尾
		# isnull：是否为null。
		# in ：是否包含在范围内。
		# year、month、day、week_day、hour、minute、second：对日期时间类型的属性进行运算。
		# 1. 评论大于等于40的图书
		# book1 = BookInfo.objects.filter( bcomment__gte=40 )
		# # 2.书名包含‘龙’字
		# book2 = BookInfo.objects.filter( btitle__contains='龙' )
		# # 3.书名以西字开头
		# book3 = BookInfo.objects.filter( btitle__startswith='西' )
		# # 4.书名以狐字结尾
		# book4 = BookInfo.objects.filter( btitle__endswith='狐' )
		# # 5.书名不为空的
		# book5 = BookInfo.objects.filter( btitle__isnull=False )
		# # 6.查询1980年发表的图书
		# # book6 = BookInfo.objects.filter( bpub_date__year=1980 )
		# # book6 = BookInfo.objects.filter( bpub_date='1980-05-01' )
		# book6 = BookInfo.objects.filter( bpub_date__gt=datetime.date(1980,5,1) )
		#
		# print( '1', book1 )
		# print( '2', book2 )
		# print( '3', book3 )
		# print( '4', book4 )
		# print( '5', book5 )
		# print( '6', book6 )

		# *****************************---get(),all(),count() - --基本查询*****************************

		# -----------------get(),all(),count() - --基本查询-----------------

		# book1 = BookInfo.objects.all()
		# print( book1 )
		# for i in book1:
		# 	print( i )
		# book2 = BookInfo.objects.get( id=2 )
		# book3 = BookInfo.objects.all().count()
		#
		# print( book1 )
		# print( book2 )
		# print( book3 )

		# *****************************---delete()，update(),save() - ---删除数据*****************************

		# -----------------delete()---删除数据---------------------
		# delete()属于物理删除
		# BookInfo.objects.filter(btitle='三国演义').delete()

		# -----------------update(),save()---删除数据---------------------

		# save()逻辑删除删除
		# book = BookInfo.objects.get( id=2 )
		# book.is_delete = True
		# book.save()

		# update()逻辑删除删除
		# BookInfo.objects.filter( btitle='天龙八部' ).update( is_delete=True )

		# *****************************---save(),update()----修改数据*****************************

		# -----------------save()----修改数据----------------------
		# book = BookInfo.objects.get( id=4 )
		# book.is_delete = True
		# book.save()

		# -----------------update()----修改数据----------------------

		# BookInfo.objects.filter( id=2 ).update( is_delete=True )
		# get()条件只能添加一个
		# BookInfo.objects.get( id=2 ).update( is_delete=True )

		# *****************************---save(),create()-----添加数据*****************************

		# -----------------create()添加数据----------------------
		# BookInfo.objects.create(
		# 	btitle='三国演义',
		# 	bpub_date='2020-10-11',
		# 	bread=200,
		# 	bcomment=22,
		# )

		# -----------------save()添加数据----------------------
		# book = BookInfo()
		# book.btitle = '西游记'
		# book.bpub_date = '1980-10-11'
		# book.bread = 100
		# book.bcomment = 88
		# book.save()

		return http.HttpResponse( '实现数据库的增删改查' )
